package reclamos;

import java.sql.*;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.*;

public class conexion 
{
    MysqlDataSource datasource;
    Connection conexion;
    Statement st;
    String estado="Ok";
    int tamaño1=0;
    int tamaño2=0;
    
    public conexion()
    {
        datasource=new MysqlDataSource();
        datasource.setUser("root");
        datasource.setPassword("root");
        datasource.setDatabaseName("reclamos2014");
        datasource.setServerName("127.0.0.1");
        try {
            conexion=datasource.getConnection();
            st=conexion.createStatement();
        } catch (SQLException ex) {
            estado=ex.getMessage();
                    }
    }
    
      void borrartablas(){
      String borrartabla ="TRUNCATE TABLE expd14";
      String borrartabla2 ="TRUNCATE TABLE flex5";
      String borrartabla3 ="TRUNCATE TABLE bagtagtemp";
      String borrartabla4 ="TRUNCATE TABLE bagtagtemp_exp";
        try {
            st.executeUpdate(borrartabla);
            st.executeUpdate(borrartabla2);
         //   st.executeUpdate(borrartabla3);
          //  st.executeUpdate(borrartabla3);
        } catch (SQLException ex) {
            Logger.getLogger(conexion.class.getName()).log(Level.SEVERE, null, ex);
        }
      
      }
      
       public void insertarAdicionales(String Expediente_Asociado, String Numero_Expediente,int contador) {
               try {
            if(!"NULL".equals(Expediente_Asociado))  {Expediente_Asociado = "'" + Expediente_Asociado + "'";}
            if(!"NULL".equals(Numero_Expediente))  {Numero_Expediente = "'" + Numero_Expediente + "'";}
            
               String cont = "'" + Integer.toString(contador) + "'";
            String insert = "INSERT INTO adicionaltemp (ADC_EXPD_ASSOC,ADC_EXPD_NMR) "
                    +"VALUES (" + Expediente_Asociado + ","+ Numero_Expediente + "," + cont + ")" ; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
    
           
    }
public void insertarBagTags_Exp(String Secuencia_equipaje,String Secuencia_expediente,String Numero_Tag, 
        String Marca,String Peso,String Color,String Tipo_Codigo,String Descripcion,
        String Fecha_expediente_nueva,String Tipo_Accion,String Usuario_Accion,int contador){
    
        try {
            if(!"NULL".equals(Secuencia_equipaje))  {Secuencia_equipaje = "'" + Secuencia_equipaje + "'";}
            if(!"NULL".equals(Secuencia_expediente))  {Secuencia_expediente = "'" + Secuencia_expediente + "'";}
            if(!"NULL".equals(Numero_Tag))  {Numero_Tag = "'" + Numero_Tag + "'";}
            if(!"NULL".equals(Marca))  {Marca = "'" + Marca+ "'";}
            if(!"NULL".equals(Peso))  {Peso = "'" + Peso + "'";}
            if(!"NULL".equals(Color))  {Color = "'" + Color + "'";}
            if(!"NULL".equals(Tipo_Codigo))  {Tipo_Codigo = "'" + Tipo_Codigo + "'";}
            if(!"NULL".equals(Descripcion))  {Descripcion = "'" + Descripcion + "'";}
            if(!"NULL".equals(Fecha_expediente_nueva))  {Fecha_expediente_nueva = "'" + Fecha_expediente_nueva + "'";}
            if(!"NULL".equals(Tipo_Accion))  {Tipo_Accion = "'" + Tipo_Accion + "'";}
            if(!"NULL".equals(Usuario_Accion))  {Usuario_Accion = "'" + Usuario_Accion + "'";}
        

            String cont = "'" + Integer.toString(contador) + "'";
            String insert = "INSERT INTO bagtagtemp_exp (BAG_EXPD_SEC_EQUIP,BAG_EXPD_SEC_EXPD,BAG_EXNMR,BAG_EXPD_MRC,BAG_EXPD_PESO,BAG_EXPD_COLOR,BAG_EXPD_TPO_CDG,BAG_EXPD_DESCRIP,BAG_EXPD_FCH_AC,BAG_EXPD_TPO_AC,BAG_EXPD_USER_AC,BAG_EXPD_CONT) "
                    +"VALUES (" + Secuencia_equipaje + "," + Secuencia_expediente + "," + Numero_Tag + "," + Marca + "," + Peso + "," + Color + "," + Tipo_Codigo + "," +Descripcion+ ","+Fecha_expediente_nueva+ ","+Tipo_Accion+ ","+Usuario_Accion+ "," + cont + ")" ; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
    
    
    
    
 }     
    public void insertarBagTags(String Linea_Aerea,String Estacion,String Tipo_expediente,
            String Numero_expediente,String Fecha_Expediente,String Secuencia,
            String Numero_BagTag,String Desc_BagTag,int contador){
        try {
            if(!"NULL".equals(Linea_Aerea))  {Linea_Aerea = "'" + Linea_Aerea + "'";}
            if(!"NULL".equals(Estacion))  {Estacion = "'" + Estacion + "'";}
            if(!"NULL".equals(Tipo_expediente))  {Tipo_expediente = "'" + Tipo_expediente + "'";}
            if(!"NULL".equals(Fecha_Expediente))  {Fecha_Expediente = "'" + Fecha_Expediente+ "'";}
            if(!"NULL".equals(Numero_expediente))  {Numero_expediente = "'" + Numero_expediente + "'";}
            if(!"NULL".equals(Secuencia))  {Secuencia = "'" + Secuencia + "'";}
            if(!"NULL".equals(Numero_BagTag))  {Numero_BagTag = "'" + Numero_BagTag + "'";}
            if(!"NULL".equals(Desc_BagTag))  {Desc_BagTag = "'" + Desc_BagTag + "'";}
            
        
        

            String cont = "'" + Integer.toString(contador) + "'";
            String insert = "INSERT INTO bagtagtemp (BAG_PRT_CDG,BAG_APR_CDG,BAG_EXPD_TPO,BAG_EXNMR,BAG_CRT_FCH,BAG_SEC,BAG_NMR,BAG_DESC,BAG_CONT) "
                    +"VALUES (" + Linea_Aerea + "," + Estacion + "," + Tipo_expediente + "," + Numero_expediente + "," + Fecha_Expediente + "," + Secuencia + "," + Numero_BagTag + "," +Desc_BagTag + "," + cont + ")" ; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
    }  
    
    public void insertarexp(String lnar, String apr, String tpo, String fch_crt, String nmr, String prd_cdg, String apr_cdg, String assoc,  String ape, String nmb, String tel, String dir, String cant, String cst, String fch_closure, String routing, String prt, String clase,String Frq, int cont) 
    {
        try {
            if(!"NULL".equals(lnar))  {lnar = "'" + lnar + "'";}
            if(!"NULL".equals(apr))  {apr = "'" + apr + "'";}
            if(!"NULL".equals(tpo))  {tpo = "'" + tpo + "'";}
            if(!"NULL".equals(fch_crt))  {fch_crt = "'" + fch_crt+ "'";}
            if(!"NULL".equals(nmr))  {nmr = "'" + nmr + "'";}
            if(!"NULL".equals(prd_cdg))  {prd_cdg = "'" + prd_cdg + "'";}
            if(!"NULL".equals(apr_cdg))  {apr_cdg = "'" + apr_cdg + "'";}
            if(!"NULL".equals(assoc))  {assoc = "'" + assoc + "'";}            
            if(!"NULL".equals(ape))  {ape = "'" + ape + "'";}
            if(!"NULL".equals(nmb))  {nmb = "'" + nmb + "'";}
            if(!"NULL".equals(tel))  {tel = "'" + tel + "'";}
            if(!"NULL".equals(dir))  {dir = "'" + dir + "'";}
            if(!"NULL".equals(cant))  {cant = "'" + cant + "'";}
            if(!"NULL".equals(cst))  {cst = "'" + cst + "'";}
            if(!"NULL".equals(fch_closure))  {fch_closure = "'" + fch_closure + "'";}
            if(!"NULL".equals(routing))  {routing = "'" + routing + "'";} 
            if(!"NULL".equals(prt))  {prt = "'" + prt + "'";}
           if(!"NULL".equals(clase))  {clase = "'" + clase + "'";}  
           if(!"NULL".equals(Frq))  {Frq = "'" + Frq + "'";}
        

             String contador = "'" + Integer.toString(cont) + "'";
            String insert = "INSERT INTO expd14 (EXPD_NMR,EXPD_TPO,EXPD_CRT_FCH,EXPD_APR_CDG,EXPD_LNAR_CDG,EXPD_PRD_CDG,EXPD_PRD_APR_CDG,EXPD_BAG_NMR,EXPD_FCH_CLOSURE,EXPD_ASSOC,EXPD_PRT_CDG,EXPD_NMB,EXPD_APE,EXPD_TEL,EXPD_DIR,EXPD_CST_TOTAL,EXPD_ROUTING,EXPD_CLASS,EXPD_FRQ_FLYR,EXPD_CONT) "
                    +"VALUES (" + nmr +"," + tpo  +"," + fch_crt + "," + apr + "," + lnar + "," + prd_cdg + "," + apr_cdg + "," + cant + "," + fch_closure + "," + assoc + "," + prt + "," + nmb + "," + ape + "," + tel + "," + dir + "," + cst + "," + routing + "," + clase + "," + Frq + "," +contador + ")"; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
    }
    
    
     public void insertarcostos(String lnar, String apr, String tpo, String fch_crt, String nmr, String sec_cst, String cdg_cst, String cdg_mon,String valor_mon,String  com_valor,int cont) 
    {
        try {
            if(!"NULL".equals(lnar))  {lnar = "'" + lnar + "'";}
            if(!"NULL".equals(apr))  {apr = "'" + apr + "'";}
            if(!"NULL".equals(tpo))  {tpo = "'" + tpo + "'";}
            if(!"NULL".equals(fch_crt))  {fch_crt = "'" + fch_crt+ "'";}
            if(!"NULL".equals(nmr))  {nmr = "'" + nmr + "'";}
            if(!"NULL".equals(sec_cst))  {sec_cst = "'" + sec_cst + "'";}
            if(!"NULL".equals(cdg_cst))  {cdg_cst = "'" + cdg_cst + "'";}
            if(!"NULL".equals(cdg_mon))  {cdg_mon = "'" + cdg_mon + "'";}            
            if(!"NULL".equals(valor_mon))  {valor_mon = "'" + valor_mon + "'";}
            if(!"NULL".equals(com_valor))  {com_valor = "'" + com_valor + "'";}
        
             String contador = "'" + Integer.toString(cont) + "'";
            String insert = "INSERT INTO Costos (CST_NMR,CST_TPO,CST_CRT_FCH,CST_APR_CDG,CST_LNAR_CDG,CST_SEC,CST_CDG,CST_MON_CDG,CST_VALOR_MON,CST_COM_VALOR,CST_CONT) "
                    +"VALUES (" + nmr +"," + tpo  +"," + fch_crt + "," + apr + "," + lnar + "," + sec_cst + "," + cdg_cst + "," + cdg_mon + "," + valor_mon + "," + com_valor + "," +contador + ")"; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
    }
    
    
    
    
    
    public void borrarrepetidos()
    {
        String truncate1 =  "TRUNCATE TABLE expdcopia";
        String insert1 = "INSERT INTO expdcopia (EXPD_NMR, EXPD_TPO, EXPD_CRT_FCH, EXPD_APR_CDG, EXPD_LNAR_CDG, EXPD_PRD_CDG, EXPD_PRD_APR_CDG, EXPD_BAG_NMR, EXPD_FCH_CLOSURE, EXPD_ASSOC, EXPD_PRT_CDG,  EXPD_APE,EXPD_NMB, EXPD_TEL, EXPD_DIR, EXPD_CST_TOTAL, EXPD_ROUTING, EXPD_CLASS, EXPD_CONT) (SELECT EXPD_NMR, EXPD_TPO, EXPD_CRT_FCH, EXPD_APR_CDG, EXPD_LNAR_CDG, EXPD_PRD_CDG, EXPD_PRD_APR_CDG, EXPD_BAG_NMR, EXPD_FCH_CLOSURE, EXPD_ASSOC, EXPD_PRT_CDG,  EXPD_APE, EXPD_NMB, EXPD_TEL, EXPD_DIR, EXPD_CST_TOTAL, EXPD_ROUTING, EXPD_CLASS, EXPD_CONT FROM expd14)";
        String delete = "DELETE from expdcopia USING expdcopia, expd14 WHERE (expdcopia.EXPD_ID < expd14.EXPD_ID) and (expdcopia.EXPD_NMR = expd14.EXPD_NMR) and (expdcopia.EXPD_APR_CDG = expd14.EXPD_APR_CDG) and (expdcopia.EXPD_LNAR_CDG = expd14.EXPD_LNAR_CDG) and (expdcopia.EXPD_TPO=expd14.EXPD_TPO)and (expdcopia.EXPD_TPO_CDG=expd14.EXPD_TPO_CDG)";
        String truncate4 = "TRUNCATE TABLE expd14";
        String insert4 = "INSERT INTO expd14 (EXPD_NMR, EXPD_TPO, EXPD_CRT_FCH, EXPD_APR_CDG, EXPD_LNAR_CDG, EXPD_PRD_CDG, EXPD_PRD_APR_CDG, EXPD_BAG_NMR, EXPD_FCH_CLOSURE, EXPD_ASSOC, EXPD_PRT_CDG, EXPD_APE,EXPD_NMB,  EXPD_TEL, EXPD_DIR, EXPD_CST_TOTAL, EXPD_ROUTING, EXPD_CLASS, EXPD_CONT) (SELECT DISTINCT EXPD_NMR, EXPD_TPO, EXPD_CRT_FCH, EXPD_APR_CDG, EXPD_LNAR_CDG, EXPD_PRD_CDG, EXPD_PRD_APR_CDG, EXPD_BAG_NMR, EXPD_FCH_CLOSURE, EXPD_ASSOC, EXPD_PRT_CDG,  EXPD_APE,EXPD_NMB, EXPD_TEL, EXPD_DIR, EXPD_CST_TOTAL, EXPD_ROUTING, EXPD_CLASS, EXPD_CONT FROM expdcopia WHERE EXPD_CRT_FCH >= '2011-01-01')";
        String updatelimaprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'LIM' WHERE e.EXPD_PRD_APR_CDG = 'JCH'";
        String updatestgoprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'SCL' WHERE e.EXPD_PRD_APR_CDG = 'ULC'";
        String updatelimaapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'LIM' WHERE e.EXPD_APR_CDG = 'JCH'";
        String updatestgoapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'SCL' WHERE e.EXPD_APR_CDG = 'ULC'";
        String updatecodprd="UPDATE expd14 e SET e.EXPD_PRD_CDG = '0' WHERE e.EXPD_PRD_CDG IS NULL";
        String updateaepprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'AEP' WHERE e.EXPD_PRD_APR_CDG = 'JNE'";
        String updatebogprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'BOG' WHERE e.EXPD_PRD_APR_CDG = 'BOD'";
        String updatecloprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'CLO' WHERE e.EXPD_PRD_APR_CDG = 'CLD'";
        String updatecorprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'COR' WHERE e.EXPD_PRD_APR_CDG = 'PJB'";
        String updateezeprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'EZE' WHERE e.EXPD_PRD_APR_CDG = 'NEZ'";
        String updategyeprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'GYE' WHERE e.EXPD_PRD_APR_CDG = 'JOJ'";
        String updatemdeprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'MDE' WHERE e.EXPD_PRD_APR_CDG = 'MDD'";
        String updatemdzprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'MDZ' WHERE e.EXPD_PRD_APR_CDG = 'EPU'";
        String updateuioprd="UPDATE expd14 e SET e.EXPD_PRD_APR_CDG = 'UIO' WHERE e.EXPD_PRD_APR_CDG = 'UEC'";
        String updateaepapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'AEP' WHERE e.EXPD_APR_CDG = 'JNE'";
        String updatebogapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'BOG' WHERE e.EXPD_APR_CDG = 'BOD'";
        String updatecloapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'CLO' WHERE e.EXPD_APR_CDG = 'CLD'";
        String updatecorapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'COR' WHERE e.EXPD_APR_CDG = 'PJB'";
        String updateezeapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'EZE' WHERE e.EXPD_APR_CDG = 'NEZ'";
        String updategyeapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'GYE' WHERE e.EXPD_APR_CDG = 'JOJ'";
        String updatemdeapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'MDE' WHERE e.EXPD_APR_CDG = 'MDD'";
        String updatemdzapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'MDZ' WHERE e.EXPD_APR_CDG = 'EPU'";
        String updateuioapr="UPDATE expd14 e SET e.EXPD_APR_CDG = 'UIO' WHERE e.EXPD_APR_CDG = 'UEC'";
        String updateroutingnull="UPDATE expd14 e SET e.EXPD_ROUTING = CONCAT(e.EXPD_PRD_APR_CDG,'/',e.EXPD_APR_CDG) WHERE e.EXPD_ROUTING IS NULL";
        
        try {
            st.executeUpdate(truncate1);
            st.executeUpdate(insert1);
            st.executeUpdate(delete);
            st.executeUpdate(truncate4);
            st.executeUpdate(insert4);
            st.executeUpdate(updatecodprd);
            // Modificación aeropuertos con siglas Baillon
            st.executeUpdate(updatelimaprd);
            st.executeUpdate(updatestgoprd);
            st.executeUpdate(updatelimaapr);
            st.executeUpdate(updatestgoapr);
            
            st.executeUpdate(updateaepprd);
            st.executeUpdate(updatebogprd);
            st.executeUpdate(updatecloprd);
            st.executeUpdate(updatecorprd);
            st.executeUpdate(updateezeprd);
            st.executeUpdate(updategyeprd);
            st.executeUpdate(updatemdeprd);
            st.executeUpdate(updatemdzprd);
            st.executeUpdate(updateuioprd);
            
            st.executeUpdate(updateaepapr);
            st.executeUpdate(updatebogapr);
            st.executeUpdate(updatecloapr);
            st.executeUpdate(updatecorapr);
            st.executeUpdate(updateezeapr);
            st.executeUpdate(updategyeapr);
            st.executeUpdate(updatemdeapr);
            st.executeUpdate(updatemdzapr);
            st.executeUpdate(updateuioapr);
            
           st.executeUpdate(updateroutingnull);
          
            } catch (SQLException ex) {
          estado= ex.getMessage();
        }
    }
        
    public void insertarvuelos(String id, String lnar, String expd_nmr, String fch, String flex_nmr, String cont, String lnar_vuelo,String tpo, String esta)
    {
        id = "'" + id + "'";
        if(!"NULL".equals(lnar))  {lnar = "'" + lnar + "'";}
        if(!"NULL".equals(expd_nmr))  {expd_nmr = "'" + expd_nmr + "'";}
        if(!"NULL".equals(fch))  {fch = "'" + fch + "'";}
        if(!"NULL".equals(flex_nmr))  {flex_nmr = "'" + flex_nmr + "'";}
        if(!"NULL".equals(cont))  {cont = "'" + cont + "'";}
        if(!"NULL".equals(lnar_vuelo))  {lnar_vuelo = "'" + lnar_vuelo + "'";}
        if(!"NULL".equals(tpo))  {tpo = "'" + tpo + "'";}
        if(!"NULL".equals(esta))  {esta = "'" + esta + "'";}
        
        String insert = "INSERT into flex5 (FLEX_ID,FLEX_LNAR_CDG,FLEX_EXPD_NMR,FLEX_FCH,FLEX_NMR,FLEX_CONT,FLEX_LNAR_VUELO, FLEX_EXPD_TPO, FLEX_EXPD_APR_CDG) VALUES (" +id+ "," + lnar + "," + expd_nmr + "," + fch + "," + flex_nmr + "," + cont + "," + lnar_vuelo + "," + tpo + "," + esta + ")";
        try {
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado=ex.getMessage();
        }
        
        
    }
    
    public void borrarrepetidosvuelos()
    {
        String truncate1 = "TRUNCATE TABLE flexcopia";
        String insert1 = "INSERT INTO flexcopia (FLEX_ID,FLEX_LNAR_CDG,FLEX_EXPD_NMR,FLEX_FCH,FLEX_NMR,FLEX_CONT,FLEX_LNAR_VUELO, FLEX_EXPD_TPO, FLEX_EXPD_APR_CDG) (SELECT FLEX_ID,FLEX_LNAR_CDG,FLEX_EXPD_NMR,FLEX_FCH,FLEX_NMR,FLEX_CONT,FLEX_LNAR_VUELO, FLEX_EXPD_TPO, FLEX_EXPD_APR_CDG FROM flex5)";
        String delete = "DELETE from flexcopia USING flexcopia, flex5 WHERE "+
                "(flexcopia.FLEX_AUX < flex5.FLEX_AUX) and (flexcopia.FLEX_EXPD_NMR = flex5.FLEX_EXPD_NMR) and (flexcopia.FLEX_EXPD_TPO =flex5.FLEX_EXPD_TPO) and (flexcopia.FLEX_EXPD_APR_CDG = flex5.FLEX_EXPD_APR_CDG) and (flexcopia.FLEX_LNAR_CDG = flex5.FLEX_LNAR_CDG)";
        String truncate2 = "TRUNCATE TABLE flex5";
        String insert2 = "INSERT INTO flex5 (FLEX_ID,FLEX_LNAR_CDG,FLEX_EXPD_NMR,FLEX_FCH,FLEX_NMR,FLEX_CONT,FLEX_LNAR_VUELO, FLEX_EXPD_TPO, FLEX_EXPD_APR_CDG) (SELECT FLEX_ID,FLEX_LNAR_CDG,FLEX_EXPD_NMR,FLEX_FCH,FLEX_NMR,FLEX_CONT,FLEX_LNAR_VUELO, FLEX_EXPD_TPO, FLEX_EXPD_APR_CDG from flexcopia)";
        String updatelimaapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'LIM' WHERE f.FLEX_EXPD_APR_CDG = 'JCH'";
        String updatestgoapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'SCL' WHERE f.FLEX_EXPD_APR_CDG = 'ULC'";
        String updateaepapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'AEP' WHERE f.FLEX_EXPD_APR_CDG = 'JNE'";
        String updatebogapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'BOG' WHERE f.FLEX_EXPD_APR_CDG = 'BOD'";
        String updatecloapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'CLO' WHERE f.FLEX_EXPD_APR_CDG = 'CLD'";
        String updatecorapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'COR' WHERE f.FLEX_EXPD_APR_CDG = 'PJB'";
        String updateezeapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'EZE' WHERE f.FLEX_EXPD_APR_CDG = 'NEZ'";
        String updategyeapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'GYE' WHERE f.FLEX_EXPD_APR_CDG = 'JOJ'";
        String updatemdeapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'MDE' WHERE f.FLEX_EXPD_APR_CDG = 'MDD'";
        String updatemdzapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'MDZ' WHERE f.FLEX_EXPD_APR_CDG = 'EPU'";
        String updateuioapr="UPDATE flex5 f SET f.FLEX_EXPD_APR_CDG = 'UIO' WHERE f.FLEX_EXPD_APR_CDG = 'UEC'";
        try {
            st.executeUpdate(truncate1);
            st.executeUpdate(insert1);
            st.executeUpdate(delete);        
            st.executeUpdate(truncate2);            
            st.executeUpdate(insert2);        
            st.executeUpdate(updatelimaapr);
            st.executeUpdate(updatestgoapr);
            //Modificación aeropuertos Siglas Baillon
            st.executeUpdate(updateaepapr);
            st.executeUpdate(updatebogapr);
            st.executeUpdate(updatecloapr);
            st.executeUpdate(updatecorapr);
            st.executeUpdate(updateezeapr);
            st.executeUpdate(updategyeapr);
            st.executeUpdate(updatemdeapr);
            st.executeUpdate(updatemdzapr);
            st.executeUpdate(updateuioapr);
            
      
            
        } catch (SQLException ex) {
            estado=ex.getMessage();
        }
        System.out.println(estado);
     }
    public void insertbagtagfinal()
    {
        String truncate="TRUNCATE TABLE tablabagtagfinal";
        
        
        String insertarbagtag_exp="INSERT INTO tablabagtagfinal (BAG_EXPD_SEC_EQUIP,BAG_EXPD_SEC_EXPD,BAG_EXPD_NMR,BAG_EXPD_MRC,BAG_EXPD_PESO,BAG_EXPD_COLOR,BAG_EXPD_TPO_CDG,BAG_EXPD_DESCRIP,BAG_EXPD_FCH_AC,BAG_EXPD_TPO_AC,BAG_EXPD_USER_AC,BAG_EXPD_CONT) SELECT  BAG_EXPD_SEC_EQUIP,BAG_EXPD_SEC_EXPD,BAG_EXPD_NMR,BAG_EXPD_MRC,BAG_EXPD_PESO,BAG_EXPD_COLOR,BAG_EXPD_TPO_CDG,BAG_EXPD_DESCRIP,BAG_EXPD_FCH_AC,BAG_EXPD_TPO_AC,BAG_EXPD_USER_AC,BAG_EXPD_CONT FROM bagtagtemp_exp";
        
  
        
        String insertarbagtag="UPDATE tablabagtagfinal t, bagtagtemp f SET t.BAG_PRT_CDG=f.BAG_PRT_CDG, t.BAG_APR_CDG=f.BAG_APR_CDG, t.BAG_EXPD_TPO = f.BAG_EXPD_TPO, t.BAG_EXNMR = f.BAG_EXNMR, t.BAG_CRT_FCH = f.BAG_CRT_FCH, t.BAG_SEC = f.BAG_SEC, t.BAG_NMR = f.BAG_NMR , t.BAG_DESC = f.BAG_DESC WHERE t.BAG_EXPD_NMR = f.BAG_NMR";
       
        try {
            st.executeUpdate(truncate);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("TRUNCATE LISTO");
            st.executeUpdate(insertarbagtag_exp);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("INSERT Bag_exp LISTO");
//            st.executeUpdate(insertarbagtag);
//            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
//            System.out.println("INSERT Bagtag LISTO");
        } catch (SQLException ex) {
            estado=ex.getMessage();
        }
        System.out.println(estado);
    
    }
    
    
    
    public void insertartablafinal()
    {
        String truncate="TRUNCATE TABLE tablafinal";
        String insertarexpd="INSERT INTO tablafinal (EXPD_LNAR_CDG,EXPD_APR_CDG,EXPD_TPO,EXPD_CRT_FCH,EXPD_NMR,EXPD_PRD_CDG,EXPD_PRD_APR_CDG,EXPD_BAG_NMR,EXPD_FCH_CLOSURE,EXPD_ASSOC,EXPD_PRT_CDG,EXPD_APE,EXPD_NMB,EXPD_TEL,EXPD_DIR,EXPD_CST_TOTAL,EXPD_ROUTING,EXPD_CLASS,EXPD_FRQ_FLYR) SELECT  EXPD_LNAR_CDG,EXPD_APR_CDG,EXPD_TPO,EXPD_CRT_FCH,EXPD_NMR,EXPD_PRD_CDG,EXPD_PRD_APR_CDG,EXPD_BAG_NMR,EXPD_FCH_CLOSURE,EXPD_ASSOC,EXPD_PRT_CDG,EXPD_APE,EXPD_NMB,EXPD_TEL,EXPD_DIR,EXPD_CST_TOTAL,EXPD_ROUTING,EXPD_CLASS,EXPD_FRQ_FLYR FROM expd14 ORDER BY EXPD_ID ASC";
        String insertarvuelos="UPDATE tablafinal t, flex5 f SET t.FLEX_ID=f.FLEX_ID, t.FLEX_FCH=f.FLEX_FCH, t.FLEX_LNAR_VUELO = f.FLEX_LNAR_VUELO, t.FLEX_NMR = f.FLEX_NMR WHERE t.EXPD_TPO = f.FLEX_EXPD_TPO  AND t.EXPD_APR_CDG = f.FLEX_EXPD_APR_CDG AND t.EXPD_LNAR_CDG = f.FLEX_LNAR_CDG AND t.EXPD_NMR = f.FLEX_EXPD_NMR";
   //     String insertarbagtag="UPDATE tablafinal t, bagtagtemp f SET t.BAG_PRT_CDG=f.BAG_PRT_CDG WHERE t.EXPD_NMR  = f.BAG_EXNMR";
     //   String insertAdicional="UPDATE tablafinal t, tablareclamos f SET t.EXPD_ASSOC=f.EXPD_ASSOC WHERE t.EXPD_NMR  = f.EXPD_NMR" + " AND f.FLEX_FCH>= '2014-01-01'";
     String insertCostos="UPDATE tablafinal t, costos c SET t.CST_SEC=c.CST_SEC, t.CST_CDG=c.CST_CDG, t.CST_VALOR_MON=c.CST_VALOR_MON, t.CST_COM_VALOR=c.CST_COM_VALOR Where t.EXPD_NMR=c.CST_NMR and t.EXPD_TPO=c.CST_TPO and t.EXPD_APR_CDG=c.CST_APR_CDG and t.EXPD_LNAR_CDG=c.CST_LNAR_CDG";
        try {
            st.executeUpdate(truncate);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("TRUNCATE LISTO");
            st.executeUpdate(insertarexpd);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("INSERT EXPD LISTO");
            st.executeUpdate(insertarvuelos);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("INSERT VUELOS LISTO");
                      st.executeUpdate(insertCostos);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("INSERT COSTOS LISTO");
//            st.executeUpdate(insertarbagtag);
//            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
//            System.out.println("INSERT BAGTAG");
              //  st.executeUpdate(insertAdicional);
            //if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
           // System.out.println("INSERT Adicionales");
        } catch (SQLException ex) {
            estado=ex.getMessage();
        }
        System.out.println(estado); 
    }
    public boolean Comprobacion(ArrayList tamaño) {

        try {

            ResultSet s = st.executeQuery("select * from tablafinal");
            s.last();
            tamaño1 = s.getRow();

        } catch (SQLException ex) {
            Logger.getLogger(conexion.class.getName()).log(Level.SEVERE, null, ex);
        }

        for (int i = 0; i < tamaño.size(); i++) {
            if (tamaño1 != (int)tamaño.get(i)){
                return false;
            }
        }

        return true;

    }
    public void QueryComprobacion(){
        String QueryC="select YEAR( t.FLEX_FCH ) AS Anio,\n" +
"MONTH( t.FLEX_FCH ) AS Mes,\n" +
"t.EXPD_TPO AS 'Tipo Expediente',\n" +
"t.EXPD_APR_CDG AS Estacion,\n" +
"t.EXPD_LNAR_CDG AS 'Linea Aerea',\n" +
"t.EXPD_NMR AS 'Numero Expediente' ,\n" +
"t.EXPD_NMB AS 'No2mbre' ,\n" +
"t.EXPD_APE AS 'Apellido',\n" +
"t.EXPD_PRD_CDG AS 'Codigo' ,\n" +
"c.nombre AS 'Descripción Codigo',\n" +
"t.EXPD_CRT_FCH AS 'Fecha Creacion',\n" +
"t.FLEX_FCH AS 'Fecha Vuelo',\n" +
"s.id_semana AS 'Semana',\n" +
"t.EXPD_FCH_CLOSURE AS 'Fecha Cierre',\n" +
"t.EXPD_PRD_APR_CDG AS 'Estacion Responsable',\n" +
"t.PAIS AS 'PAIS',\n" +
"max(t.EXPD_CST_TOTAL) AS 'Costo Total',\n" +
"t.EXPD_ROUTING AS Routing,\n" +
"t.FLEX_NMR AS 'Numero Vuelo',\n" +
"t.EXPD_CLASS AS Clase ,\n" +
"t.RUTA_INF AS Ruta_INF,\n" +
"t.TIPO_CLASE AS 'Tipo Clase',\n" +
"t.TIEMPO_CIERRE 'Demora Cierre',\n" +
"t.CUMPLE_META AS 'Cumple Meta Crr',\n" +
"c.gestionable as Gestionable,\n" +
"c.tipo as Tipo\n" +
"FROM tablafinal t, semanas2 s, codigos_scl c\n" +
"WHERE s.fch = t.FLEX_FCH\n" +
"AND t.EXPD_PRD_CDG = c.id_codigo AND t.FLEX_FCH >= '2013-01-01'\n" +
"and t.EXPD_PRD_CDG != '94'\n" +
"and t.EXPD_PRD_CDG != '93'\n" +
"and t.EXPD_PRD_CDG != '79'\n" +
"and t.EXPD_PRD_CDG != '45'\n" +
"group by YEAR( t.FLEX_FCH ),MONTH( t.FLEX_FCH ),t.EXPD_TPO,t.EXPD_APR_CDG,t.EXPD_LNAR_CDG,\n" +
"t.EXPD_NMR,\n" +
"t.EXPD_NMB,\n" +
"t.EXPD_APE,\n" +
"t.EXPD_PRD_CDG,\n" +
"c.nombre,\n" +
"t.EXPD_CRT_FCH,\n" +
"t.FLEX_FCH,\n" +
"s.id_semana,\n" +
"t.EXPD_FCH_CLOSURE,\n" +
"t.EXPD_PRD_APR_CDG,\n" +
"t.PAIS,\n" +
"t.EXPD_ROUTING,\n" +
"t.FLEX_NMR,\n" +
"t.EXPD_CLASS,\n" +
"t.RUTA_INF,\n" +
"t.TIPO_CLASE,\n" +
"t.TIEMPO_CIERRE,\n" +
"t.CUMPLE_META ";
    try {
            st.executeUpdate(QueryC);
            if(!estado.equals("Ok")){System.out.println(estado); estado = "Ok";}
            System.out.println("Query LISTO");
    }catch (SQLException ex) {
            estado=ex.getMessage();
        }
        System.out.println(estado); 
    
    }
    
    public void insertarnegocio()
    {
        String truncate = "TRUNCATE TABLE tablatemp";
        String insert="INSERT INTO tablatemp (TIPO,APR,LNAR,NMR,ORI,DES) SELECT e.EXPD_TPO ,e.EXPD_APR_CDG,e.EXPD_LNAR_CDG,e.EXPD_NMR,CASE WHEN OCTET_LENGTH(e.EXPD_ROUTING) = 7 then substr(e.EXPD_ROUTING,1,3) WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) != 0 AND substr(e.EXPD_ROUTING,instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) + 4,3) != '' then substr(e.EXPD_ROUTING, instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG),3) 	WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) = 0 then substr(e.EXPD_ROUTING,1,3) WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) != 0 AND substr(e.EXPD_ROUTING,instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) + 4,3) = '' then substr(e.EXPD_ROUTING, (instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG)-4),3) END as ORIGEN, CASE WHEN OCTET_LENGTH(e.EXPD_ROUTING) = 7 then substr(e.EXPD_ROUTING,5,3) WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) != 0 AND substr(e.EXPD_ROUTING,instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) + 4,3) != '' then substr(e.EXPD_ROUTING, instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG)+4,3)	WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) = 0 then substr(e.EXPD_ROUTING,4,3) 	WHEN OCTET_LENGTH(e.EXPD_ROUTING) != 7 AND instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) != 0 AND substr(e.EXPD_ROUTING,instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG) + 4,3) = '' then substr(e.EXPD_ROUTING, instr(e.EXPD_ROUTING,e.EXPD_PRD_APR_CDG),3) END as DESTINO from expd14 e"+"WHERE FLEX_FCH> 2013-12-30";
        String update="UPDATE tablafinal t, tablatemp p SET t.EXPD_ORI = p.ORI, t.EXPD_DES = p.DES WHERE t.EXPD_TPO = p.TIPO AND t.EXPD_APR_CDG = p.APR AND t.EXPD_LNAR_CDG = p.LNAR AND t.EXPD_NMR = p.NMR";
        String updatenegocio1="UPDATE tablafinal t,dicvuelos4 d SET t.NEGOCIO = d.negocio WHERE t.EXPD_ORI = d.origen AND t.EXPD_DES = d.destino";
        String updatenegocio2="UPDATE tablafinal t SET t.NEGOCIO = 'LH' WHERE t.EXPD_PRD_APR_CDG = 'AKL'";
        String updatenegocio3="UPDATE tablafinal t SET t.NEGOCIO = 'NO VALIDO' WHERE t.EXPD_PRD_APR_CDG != t.EXPD_ORI AND t.EXPD_PRD_APR_CDG != t.EXPD_DES";
        //String updatenegocio4="UPDATE tablafinal t SET t.EXPD_ORI = t.EXPD_PRD_APR_CDG, t.EXPD_DES = t.EXPD_APR_CDG WHERE t.NEGOCIO = 'NO VALIDO'";
        //String updatenegocio5="UPDATE tablafinal t,dicvuelos4 d SET t.NEGOCIO = d.negocio WHERE t.EXPD_ORI = d.origen AND t.EXPD_DES = d.destino";
        String updaterutainter="UPDATE tablafinal t SET t.RUTA_INF = 'INT' WHERE t.NEGOCIO = 'LH' OR t.NEGOCIO='REG'";
        String updaterutadom="UPDATE tablafinal t SET t.RUTA_INF = 'DOM' WHERE t.NEGOCIO = 'DOMLA' OR t.NEGOCIO='DOMCO' OR t.NEGOCIO = 'DOMLP' OR t.NEGOCIO = 'DOMEC' OR t.NEGOCIO='DOMXL' OR t.NEGOCIO='DOM4M'";
        String updaterutanull="UPDATE tablafinal t SET t.RUTA_INF = 'NO VALIDO' WHERE t.RUTA_INF IS NULL";
        String updatepais1="UPDATE tablafinal t, aeropuertos a SET t.PAIS = a.PAIS WHERE t.EXPD_PRD_APR_CDG = a.ATO";
        String updatepais2="UPDATE tablafinal t SET t.PAIS = 'INT' WHERE t.PAIS IS NULL";
        String updatetipoclase="UPDATE tablafinal t,clasestarifarias c SET t.TIPO_CLASE = c.GRUPO WHERE t.EXPD_CLASS = c.CLASE";
        String updatetipoclase2="UPDATE tablafinal t SET t.TIPO_CLASE = 'ECONOMY' WHERE t.TIPO_CLASE IS NULL";
        String updatetiempocierre = "UPDATE tablafinal t SET t.TIEMPO_CIERRE = DATEDIFF(t.EXPD_FCH_CLOSURE,t.EXPD_CRT_FCH) WHERE t.EXPD_FCH_CLOSURE IS NOT NULL AND t.EXPD_CRT_FCH IS NOT NULL";
        String updatecumplemeta_ahl_elite="UPDATE tablafinal t SET t.CUMPLE_META = 'SI', t.META_ESPERADA = '95' WHERE t.EXPD_TPO = 'AHL' AND t.TIEMPO_CIERRE <= '2' AND t.TIPO_CLASE = 'ELITE' AND t.EXPD_FCH_CLOSURE IS NOT NULL";
        String updatecumplemeta_ahl_economy="UPDATE tablafinal t SET t.CUMPLE_META = 'SI', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'AHL' AND t.TIEMPO_CIERRE <= '2' AND t.TIPO_CLASE = 'ECONOMY' AND t.EXPD_FCH_CLOSURE IS NOT NULL";
        String updatecumplemeta_ahl_null="UPDATE tablafinal t SET t.CUMPLE_META = 'SI' WHERE t.EXPD_TPO ='AHL' AND t.TIEMPO_CIERRE <= '2'";
        String updatenocumplemeta_ahl_elite="UPDATE tablafinal t SET t.CUMPLE_META = 'NO', t.META_ESPERADA = '95' WHERE t.EXPD_TPO = 'AHL' AND t.TIEMPO_CIERRE > '2' AND t.TIPO_CLASE = 'ELITE'";
        String updatenocumplemeta_ahl_economy="UPDATE tablafinal t SET t.CUMPLE_META = 'NO', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'AHL' AND t.TIEMPO_CIERRE > '2' AND t.TIPO_CLASE = 'ECONOMY'";    
        String updatecumplemeta_dpr1="UPDATE tablafinal t SET t.CUMPLE_META = 'SI', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE <= '5' AND t.NEGOCIO != 'DOMLP'";
        String updatecumplemeta_dpr2="UPDATE tablafinal t SET t.CUMPLE_META = 'SI', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE <= '10' AND t.NEGOCIO = 'DOMLP'";
        String updatecumplemeta_dpr3="UPDATE tablafinal t SET t.CUMPLE_META = 'SI', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE <= '5' AND t.PAIS != 'PERU' AND t.CUMPLE_META IS NULL";
        String updatenocumplemeta_dpr1="UPDATE tablafinal t SET t.CUMPLE_META = 'NO', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE > '5' AND t.NEGOCIO != 'DOMLP'";
        String updatenocumplemeta_dpr2="UPDATE tablafinal t SET t.CUMPLE_META = 'NO', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE > '10' AND t.NEGOCIO = 'DOMLP'";
        String updatenocumplemeta_dpr3="UPDATE tablafinal t SET t.CUMPLE_META = 'NO', t.META_ESPERADA = '92' WHERE t.EXPD_TPO = 'DPR' AND t.TIEMPO_CIERRE > '5' AND t.PAIS != 'PERU' AND t.CUMPLE_META IS NULL";
        //String borrarcasosextra="DELETE FROM tablafinal USING tablafinal WHERE tablafinal.EXPD_PRD_CDG='0'";
        try {
            st.executeUpdate(truncate);
            st.executeUpdate(insert);
            st.executeUpdate(update);
            st.executeUpdate(updatenegocio1);
            st.executeUpdate(updatenegocio2);
            st.executeUpdate(updatenegocio3);
            //st.executeUpdate(updatenegocio4);
            //st.executeUpdate(updatenegocio5);
            st.executeUpdate(updaterutainter);
            st.executeUpdate(updaterutadom);
            st.executeUpdate(updaterutanull);
            st.executeUpdate(updatepais1);
            st.executeUpdate(updatepais2);
            st.executeUpdate(updatetipoclase);
            st.executeUpdate(updatetipoclase2);
            st.executeUpdate(updatetiempocierre);
            st.executeUpdate(updatecumplemeta_ahl_elite);
            st.executeUpdate(updatecumplemeta_ahl_economy);
            st.executeUpdate(updatenocumplemeta_ahl_elite);
            st.executeUpdate(updatenocumplemeta_ahl_economy);
            st.executeUpdate(updatecumplemeta_ahl_null);
            st.executeUpdate(updatecumplemeta_dpr1);
            st.executeUpdate(updatecumplemeta_dpr2);
            st.executeUpdate(updatecumplemeta_dpr3);
            st.executeUpdate(updatenocumplemeta_dpr1);
            st.executeUpdate(updatenocumplemeta_dpr2);
            st.executeUpdate(updatenocumplemeta_dpr3);
            //st.executeUpdate (borrarcasosextra);
            conexion.close();
        } catch (SQLException ex) {
            estado=ex.getMessage();
        }
    
    }   
    boolean BDVacia(String BD){
        int count=0;
        int flag=0;
        try {
            String query= "Select * FROM "+BD ;
            ResultSet rs=st.executeQuery(query);
            if(!rs.next())flag=1;
        } catch (SQLException ex) {
            Logger.getLogger(conexion.class.getName()).log(Level.SEVERE, null, ex);
        }
         if (flag==1){
                return true;
            }
         else 
                return false;
    }

    void insertarGestionable(String Cd, String Descripcion,String Responsable,String Gestionables,String Tipo, int contador) {
        
        try {
            if(!"NULL".equals(Cd))  {Cd = "'" + Cd + "'";}
            if(!"NULL".equals(Descripcion))  {Descripcion = "'" + Descripcion + "'";}
            if(!"NULL".equals(Responsable))  {Responsable = "'" + Responsable + "'";}
            if(!"NULL".equals(Gestionables))  {Gestionables = "'" + Gestionables+ "'";}
            if(!"NULL".equals(Tipo))  {Tipo = "'" + Tipo + "'";}
            
        

             String cont = "'" + Integer.toString(contador) + "'";
            String insert = "INSERT INTO gestionable (Cd,Descripcion,Responsable,Gestionables,Tipo,Cont) "
                    +"VALUES (" + Cd +"," + Descripcion  +"," + Responsable + "," + Gestionables + "," + Tipo +","+contador + ")"; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
        
        
        
    }

    void insertarClasAto(String ATO, String FILIAL_CDG, String FILIAL_DSC, String RUTA_CDG, String RUTA_DSC, String Gerente_ATO, int contador) {
         try {
            if(!"NULL".equals(ATO))  {ATO = "'" + ATO + "'";}
            if(!"NULL".equals(FILIAL_CDG))  {FILIAL_CDG = "'" + FILIAL_CDG + "'";}
            if(!"NULL".equals(FILIAL_DSC))  {FILIAL_DSC = "'" + FILIAL_DSC + "'";}
            if(!"NULL".equals(RUTA_CDG))  {RUTA_CDG = "'" + RUTA_CDG+ "'";}
            if(!"NULL".equals(RUTA_DSC))  {RUTA_DSC = "'" + RUTA_DSC + "'";}
            if(!"NULL".equals(Gerente_ATO))  {Gerente_ATO = "'" + Gerente_ATO + "'";}
        

             String cont = "'" + Integer.toString(contador) + "'";
            String insert = "INSERT INTO clasato (ATO,FILIAL_CDG,FILIAL_DSC,RUTA_CDG,RUTA_DSC,Gerente_ATO,Cont) "
                    +"VALUES (" + ATO +"," + FILIAL_CDG  +"," + FILIAL_DSC + "," + RUTA_CDG + "," + RUTA_DSC +","+Gerente_ATO+","+contador + ")"; 
            st.executeUpdate(insert);
        } catch (SQLException ex) {
            estado = ex.getMessage();
        }
        
    }

    void insertarcostos(String Linea_Aerea, String Estacion, String Tipo_expediente, String Fecha_expediente, String Numero_expediente, String Secuencia_Costo, String Valor_Moneda, String Codigo_Moneda, String Comentario_valor, int contador) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

  
    

    
    }


/*
select YEAR( t.FLEX_FCH ) AS Anio,
MONTH( t.FLEX_FCH ) AS Mes,
t.EXPD_TPO AS 'Tipo Expediente',
t.EXPD_APR_CDG AS Estacion,
t.EXPD_LNAR_CDG AS 'Linea Aerea',
t.EXPD_NMR AS 'Numero Expediente' ,
t.EXPD_NMB AS 'Nombre' ,
t.EXPD_APE AS 'Apellido',
t.EXPD_PRD_CDG AS 'Codigo' ,
c.Descripcion AS 'Descripción Codigo',
t.EXPD_CRT_FCH AS 'Fecha Creacion',
t.FLEX_FCH AS 'Fecha Vuelo',
s.id_semana AS 'Semana',
t.EXPD_FCH_CLOSURE AS 'Fecha Cierre',
t.EXPD_PRD_APR_CDG AS 'Estacion Responsable',
t.PAIS AS 'PAIS',
max(t.EXPD_CST_TOTAL) AS 'Costo Total',
t.EXPD_ROUTING AS Routing,
t.FLEX_NMR AS 'Numero Vuelo',
t.EXPD_CLASS AS Clase ,
t.RUTA_INF AS Ruta_INF,
a.FILIAL_CDG AS 'Tipo Clase',
t.TIEMPO_CIERRE 'Demora Cierre',
t.CUMPLE_META AS 'Cumple Meta Crr',
t.EXPD_FRQ_FLYR as 'Frequent Flyer',
c.Gestionables as Gestionable,
c.Tipo as Tipo
FROM tablafinal t, semanas2 s, gestionable c, clasato a
WHERE s.fch = t.FLEX_FCH and a.ATO=t.EXPD_PRD_CDG
AND t.EXPD_PRD_CDG = c.Cd AND t.FLEX_FCH >= '2013-01-01'
and t.EXPD_PRD_CDG != '94'
and t.EXPD_PRD_CDG != '93'
and t.EXPD_PRD_CDG != '79'
and t.EXPD_PRD_CDG != '45'
group by YEAR( t.FLEX_FCH ),MONTH( t.FLEX_FCH ),t.EXPD_TPO,t.EXPD_APR_CDG,t.EXPD_LNAR_CDG,
t.EXPD_NMR,
t.EXPD_NMB,
t.EXPD_APE,
t.EXPD_PRD_CDG,
c.Descripcion,
t.EXPD_CRT_FCH,
t.FLEX_FCH,
s.id_semana,
t.EXPD_FCH_CLOSURE,
t.EXPD_PRD_APR_CDG,
t.PAIS,
t.EXPD_ROUTING,
t.FLEX_NMR,
t.EXPD_CLASS,
t.RUTA_INF,
t.TIPO_CLASE,
t.TIEMPO_CIERRE,
t.CUMPLE_META, 
t.EXPD_FRQ_FLYR



*/
